--select ScenarioId, ProviderCompanyId, min(AnnualPremium) MinAnnualPremium, max(AnnualPremium) MaxAnnualPremium, CarMakeId, CarModelId
--from tblCarInsuranceResult Group By  ScenarioId, ProviderCompanyId, CarMakeId, CarModelId
--Order By ScenarioId , CarMakeId, CarModelId, min(AnnualPremium)

--select ScenarioId, ProviderCompanyId, min(AnnualPremium) MinAnnualPremium, max(AnnualPremium) MaxAnnualPremium
--into tblPremiumRankTemp
--from  tblCarInsuranceResult Group By  ScenarioId, ProviderCompanyId
--Order By ScenarioId, min(AnnualPremium)

Delete from tblPremiumRankTemp

INSERT INTO tblPremiumRankTemp(ScenarioId, ProviderCompanyId, MinAnnualPremium, MaxAnnualPremium, CarMakeId, CarModelId)
SELECT ScenarioId, ProviderCompanyId, min(AnnualPremium) MinAnnualPremium, max(AnnualPremium) MaxAnnualPremium, CarMakeId, CarModelId
FROM  tblCarInsuranceResult GROUP BY  ScenarioId, ProviderCompanyId, CarMakeId, CarModelId
ORDER BY ScenarioId, CarMakeId, CarModelId, min(AnnualPremium)

Select * from tblPremiumRankTemp
--Select top 2 ProviderCompanyId from tblPremiumRankTemp Where ScenarioId = 1

Declare @ScenarioId int
Declare @CarMakeId int
Declare @CarModelId int
Declare @ScenarioCount int

--Select @ScenarioCount = Count(ScenarioId) from tblPremiumRankTemp



Declare curScenario cursor for
Select distinct ScenarioId, CarMakeId, CarModelId from tblPremiumRankTemp Order By ScenarioId, CarMakeId, CarModelId

Delete from tblPremiumRankByCompanyTemp
Delete from tblPremiumRankByCarMakeModel

Insert Into tblPremiumRankByCompanyTemp(ProviderCompanyId, FirstCount, SecondCount, ThirdCount, FourthCount, FifthCount)
Select distinct ProviderCompanyId, 0, 0, 0, 0, 0 from tblPremiumRankTemp

--Select * from tblPremiumRankByCompanyTemp
Set @ScenarioCount = 0

Open curScenario 
		fetch next from curScenario into @ScenarioId, @CarMakeId, @CarModelId
		
		WHILE (@@fetch_status <> -1)
		BEGIN
			Declare curPremiumRank cursor for
			Select ProviderCompanyId from tblPremiumRankTemp Where ScenarioId = @ScenarioId And CarMakeId = @CarMakeId And CarModelId = @CarModelId
			
			Select @ScenarioCount = @ScenarioCount + COUNT(ProviderCompanyId) from tblPremiumRankTemp Where ScenarioId = @ScenarioId And CarMakeId = @CarMakeId And CarModelId = @CarModelId
			
			Declare @ProviderCompanyId bigint
			
			Open curPremiumRank
			fetch next from curPremiumRank into @ProviderCompanyId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set FirstCount = FirstCount + 1 Where ProviderCompanyId = @ProviderCompanyId
				
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CarMakeId, CarModelId, Position)
				Values(@ProviderCompanyId, @ScenarioId, @CarMakeId, @CarModelId, 1)
			END
			
			fetch next from curPremiumRank into @ProviderCompanyId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set SecondCount = SecondCount + 1 Where ProviderCompanyId = @ProviderCompanyId
				
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CarMakeId, CarModelId, Position)
				Values(@ProviderCompanyId, @ScenarioId, @CarMakeId, @CarModelId, 2)
			END
			
			fetch next from curPremiumRank into @ProviderCompanyId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set ThirdCount = ThirdCount + 1 Where ProviderCompanyId = @ProviderCompanyId
				
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CarMakeId, CarModelId, Position)
				Values(@ProviderCompanyId, @ScenarioId, @CarMakeId, @CarModelId, 3)
			END
				
			
			fetch next from curPremiumRank into @ProviderCompanyId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set FourthCount = FourthCount + 1 Where ProviderCompanyId = @ProviderCompanyId
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CarMakeId, CarModelId, Position)
				
				Values(@ProviderCompanyId, @ScenarioId, @CarMakeId, @CarModelId, 4)
			END
			
			fetch next from curPremiumRank into @ProviderCompanyId
			IF (@@fetch_status <> -1)
			BEGIN
				Update tblPremiumRankByCompanyTemp Set FifthCount = FifthCount + 1 Where ProviderCompanyId = @ProviderCompanyId
			
				INSERT INTO tblPremiumRankByCarMakeModel(ProviderCompanyId, ScenarioId, CarMakeId, CarModelId, Position)
				Values(@ProviderCompanyId, @ScenarioId, @CarMakeId, @CarModelId, 5)
			END
			
			Close curPremiumRank
			Deallocate curPremiumRank
			
			fetch next from curScenario into @ScenarioId, @CarMakeId, @CarModelId
		END
Close curScenario
Deallocate curScenario

Select @ScenarioCount

Select ProviderCompanyId, 
(Convert(float,FirstCount)/(@ScenarioCount/5) * 100) as FirstPercent, 
(Convert(float,SecondCount)/(@ScenarioCount/5) * 100) as SecondPercent,
(Convert(float,ThirdCount)/(@ScenarioCount/5) * 100) as ThirdPercent,
(Convert(float,FourthCount)/(@ScenarioCount/5) * 100) as ThirdPercent,
(Convert(float,FifthCount)/(@ScenarioCount/5) * 100) as ThirdPercent
from dbo.tblPremiumRankByCompanyTemp

Select ProviderCompanyId, 
(Convert(float,FirstCount)/56) as FirstPercent, 
(Convert(float,SecondCount)/56) as SecondPercent,
(Convert(float,ThirdCount)/56) as ThirdPercent,
(Convert(float,FourthCount)/56) as ThirdPercent, 
(Convert(float,FifthCount)/56) as ThirdPercent
from dbo.tblPremiumRankByCompanyTemp

--Scenario Count 5857
--Company Count 5
Select ProviderCompanyId, 
(Convert(float,FirstCount)/(5857/5)) as FirstPercent, 
(Convert(float,SecondCount)/(5857/5)) as SecondPercent,
(Convert(float,ThirdCount)/(5857/5)) as ThirdPercent,
(Convert(float,FourthCount)/(5857/5)) as ThirdPercent,
(Convert(float,FifthCount)/(5857/5)) as ThirdPercent
from dbo.tblPremiumRankByCompanyTemp


Select ProviderCompanyId, 
(Convert(float,FirstCount)/56) as FirstPercent, 
(Convert(float,SecondCount)/56) as SecondPercent,
(Convert(float,ThirdCount)/56) as ThirdPercent,
(Convert(float,FourthCount)/56) as ThirdPercent, 
(Convert(float,FifthCount)/56) as ThirdPercent
from dbo.tblPremiumRankByCompanyTemp

SELECT     S.ScenarioNo, PC.CompanyName, CM.Description, CM.Body, CM.CC, R.Position
FROM         tblPremiumRankByCarMakeModel AS R INNER JOIN
                      tblCarModelInfo AS CM ON R.CarModelId = CM.CarModelId AND R.CarMakeId = CM.CarMakeId INNER JOIN
                      tblProviderCompany AS PC ON R.ProviderCompanyId = PC.ProviderCompanyId INNER JOIN
                      tblScenario AS S ON R.ScenarioId = S.ScenarioID
ORDER BY R.ScenarioId, R.CarMakeId, R.CarModelId, PC.CompanyName, R.Position

--Select * from tblPremiumRankByCompanyTemp
--Select ScenarioId, CarMakeId, CarModelId, MinAnnualPremium, ProviderCompanyId from tblPremiumRankTemp 
--Order By ScenarioId, CarMakeId, CarModelId, MinAnnualPremium, ProviderCompanyId




